-- @owner:yangjian
-- @date:2024-3-26
-- @testpoint:1.去除大对象使用限制，测试lo_create函数使用，合理报错

--清理环境
CREATE OR REPLACE FUNCTION f_opengauss_large_object_function_case0002_1()  
RETURNS VOID AS 
$$
BEGIN  
    BEGIN
    PERFORM lo_unlink(100);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(-2147483648);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(4294967295);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(-10);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(200);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(1);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(2);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
END;  
$$
LANGUAGE plpgsql;
/

select f_opengauss_large_object_function_case0002_1();

--环境准备
drop table if exists t_opengauss_large_object_function_case0002_2 cascade;
CREATE TABLE t_opengauss_large_object_function_case0002_2 (a int);
insert into t_opengauss_large_object_function_case0002_2 values (1);
insert into t_opengauss_large_object_function_case0002_2 values (2);

--step1:创建表存储函数返回值;expect:成功
drop table if exists t_opengauss_large_object_function_case0002_1 cascade;
CREATE TABLE t_opengauss_large_object_function_case0002_1 (loid oid,fd int);

--step2:测试参数为普通正数，验证返回值为参数值;expect:成功
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(100);
select loid from t_opengauss_large_object_function_case0002_1;

--step3:测试参数为0;expect:成功
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(0);

--step4:测试参数为负数;expect:成功
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(-10);

--step5:测试参数为int4边界值;expect:成功
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(4294967295);
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(-2147483648);

--step6:测试参数超int4边界值;expect:失败
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(4294967296);
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(-2147483649);

--step7:测试参数为违规值;expect:失败
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create('a');
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create();
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(@);
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create('@');
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create("a");
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create("1");
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create('@$#@#@$@$#@!%$#%$#%@$#%$#%@$#%$#!%#$%$@%#$%@#$%$@%$#@%#$@%$#%@FDDF%$@#%^%%^#@^#^@$#%^#@');
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(21474836465476567546765476675474657456745756475765745676547546754675647547576443453454344444476575675647567546745675467354353465475674564564444466666668);

--step8:测试参数为空值;expect:成功
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create('');
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(null);

--step9:测试参数为表的列;expect:成功
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(a) from t_opengauss_large_object_function_case0002_2;

--step10:测试参数为已存在的oid;expect:成功
SELECT lo_create(200);
INSERT INTO t_opengauss_large_object_function_case0002_1 (loid) SELECT lo_create(200);

--清理环境
select lo_unlink(loid) from t_opengauss_large_object_function_case0002_1;
select f_opengauss_large_object_function_case0002_1();
drop table if exists t_opengauss_large_object_function_case0002_1 cascade;
drop table if exists t_opengauss_large_object_function_case0002_2 cascade;
drop function f_opengauss_large_object_function_case0002_1();
